#!/user/bin/env python3
# -*- coding: utf-8 -*-
import sys
import xlrd,xlwt
import csv
import numpy as np
import pandas as pd
from pandas import Series, DataFrame

# Series
obj1 = Series([4, 7, -5, 3], index=['d', 'b', 'a', 'c'])
# print(obj1['d'])

obj1['d'] = 6
# print(obj1[['c', 'a', 'd']])

# print(obj1[obj1 > 0])
# print(obj1 * 2)
# print(np.exp(obj1))

sdata = {'Ohio': 35000, 'Texas': 71000, 'Oregon': 16000, 'Utah': 5000}
obj2 = Series(sdata)
# print(obj2)

states = ['California', 'Ohio', 'Oregon', 'Texas']
obj3 = Series(sdata, index=states)
# print(obj3)

# print(obj2 + obj3)

obj3.name = 'population'
obj3.index.name = 'state'
# print(obj3)


# dataFrame
data = {'state': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],
        'year': [2000, 2001, 2002, 2001, 2002],
        'pop': [1.5, 1.7, 3.6, 2.4, 2.9]}
frame = DataFrame(data)
# print(frame)

# print(DataFrame(data, columns=['year', 'state', 'pop']))

frame2 = DataFrame(data, columns=['year', 'state', 'pop', 'debt'],
                   index=['one', 'two', 'three', 'four', 'five'])
# print(frame2)
# print(frame2['state'])
# print(frame2.year)
# print(frame2.index)
frame2['debt'] = np.arange(5.)
# print(frame2)

val = Series([-1.2, -1.5, -1.7], index=['two', 'four', 'five'])
frame2['debt'] = val
# print(frame2)

frame2['eastern'] = frame2.state == 'Ohio'
# print(frame2)
del frame2['eastern']
# print(frame2.columns)

pop = {'Nevada': {2001: 2.4, 2002: 2.9},
       'Ohio': {2000: 1.5, 2001: 1.7, 2002: 3.6}}
frame3 = DataFrame(pop)
# print(frame3)

# print(DataFrame(pop, index=[2001, 2002, 2003]))
pdata = {'Ohio': frame3['Ohio'][:-1],
         'Nevada': frame3['Nevada'][:2]}
# print(DataFrame(pdata))

frame3.index.name = 'year'
frame3.columns.name = 'state'
# print(frame3)

# 索引对象
obj = Series(range(3), index=['a', 'b', 'c'])
index = obj.index
# print(index)

index = pd.Index(np.arange(3))
obj2 = Series([1.5, -2.5, 0], index=index)
# print(obj2.index is index)
# print(frame3)

# 数据读取
# 读取文本格式数据
df = pd.read_csv('data/ex1.csv')
# print(df)
#
# print(pd.read_table('data/ex1.csv', sep=','))
# print(pd.read_csv('data/ex2.csv', header=None))
# print(pd.read_csv('data/ex2.csv', names=['a', 'b', 'c', 'd', 'message']))
#
# names = ['a', 'b', 'c', 'd', 'message']
# print(pd.read_csv('data/ex2.csv', names=names, index_col='message'))

# parsed = pd.read_csv('data/csv_mindex.csv', index_col=['key1', 'key2'])
# print(parsed)
#
# result = pd.read_csv('data/ex3.txt', sep='\s+')
# print(result)
#
# print(pd.read_csv('data/ex5.csv', skiprows=[0, 2, 3]))
#
# result = pd.read_csv('data/ex5.csv')
# print(result)
# result = pd.read_csv('data/ex5.csv', na_values=['NULL'])
# print(result)
#
# sentinels = {'message': ['foo', 'NA'], 'something': ['two']}
# print(pd.read_csv('data/ex5.csv', na_values=sentinels))

# 逐行读取文本文件
# result = pd.read_csv('data/ex6.csv', nrows=5)
# print(result)
#
# chunker = pd.read_csv('data/ex6.csv', chunksize=1000)
# tot = Series([])
# for piece in chunker:
#     tot = tot.add(piece['key'].value_counts(), fill_value=0)
# tot = tot.sort_values(ascending=False)
# print(tot[:10])

# 文件写出
# data = pd.read_csv('data/ex5.csv')
# print(data)
#
# data.to_csv('data/out.csv')
# data.to_csv(sys.stdout, sep='|')
# print('\n')
# data.to_csv(sys.stdout, na_rep='NULL')
# print('\n')
# data.to_csv(sys.stdout, index=False, header=False)
# print('\n')
# data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])
#
# dates = pd.date_range('1/1/2000', periods=7)
# ts = Series(np.arange(7), index=dates)
# ts.to_csv('tseries.csv')

# Series.read_csv('tseries.csv', parse_dates=True)

# 手工处理分隔符

f = open('data/ex7.csv')
reader = csv.reader(f)

for line in reader:
    print(line)

lines = list(csv.reader(open('data/ex7.csv')))
header, values = lines[0], lines[1:]
data_dict = {h: v for h, v in zip(header, zip(*values))}
print(data_dict)


class My_Dialect(csv.Dialect):
    lineterminator = '\n'
    delimiter = ';'
    quotechar = '"'
    quoting = csv.QUOTE_MINIMAL


with open('mydata.csv', 'w') as f:
    writer = csv.writer(f, dialect=My_Dialect)
    writer.writerow(('one', 'two', 'three'))
    writer.writerow(('1', '2', '3'))
    writer.writerow(('4', '5', '6'))
    writer.writerow(('7', '8', '9'))
pd.read_table('mydata.csv', sep=';')

# 生成xls工作簿
path = 'data/'

wb = xlwt.Workbook()
wb.add_sheet('first_sheet', cell_overwrite_ok=True)
wb.get_active_sheet()

ws_1 = wb.get_sheet(0)

ws_2 = wb.add_sheet('second_sheet')
data = np.arange(1, 65).reshape((8, 8))
ws_1.write(0, 0, 100)

for c in range(data.shape[0]):
    for r in range(data.shape[1]):
        ws_1.write(r, c, data[c, r])
        ws_2.write(r, c, data[r, c])
wb.save(path + 'workbook.xls')

# 从工作簿中读取
book = xlrd.open_workbook(path + 'workbook.xls')
